﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using UFC.DBUtility;
using System.Collections;
using System.Configuration;

namespace SendIndustrialNetwork
{
    /*-----------------------------------------------------
     *-------产线系统-工序级任务下发产线相关表-------
     */
    public class SaveDgMESData
    {
        //public static string toAppId = ConfigurationManager.AppSettings["ToAppID_GX"].ToString();//推送数据中心目的ID（导管）

        public void Excute()
        {
            DataTable dispatchDT = getDgWorkflowStepData();
            foreach (DataRow row in dispatchDT.Rows)
            {
                string toAppId = row["toAppid"].ToString();
                DataTable dispatchCopy = dispatchDT.Clone();
                dispatchCopy.Rows.Add(row.ItemArray);
                DataView dispatchDV = dispatchCopy.AsDataView();
                /*推送批次信息*/
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "containerid"), "container", "containerid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "containerid"), "containercurrentinfo", "containerid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "containerstepdispatchid"), "containerstepdispatch", "containerstepdispatchid", toAppId, CommonMethods.OpType.新增);

                /*推送批次子序号信息*/
                var sql = string.Format("SELECT c.containerid FROM container c WHERE c.parentcontainerid = '{0}' AND c.status = 1", row["containerid"].ToString());
                DataTable dtChildCon = OracleHelper.QueryTable(sql, null);
                if (dtChildCon.Rows.Count > 0)
                {
                    CommonMethods.InsertTableChangeRecord(dtChildCon, "container", "containerid", toAppId, CommonMethods.OpType.新增);
                }
                /*推送批次工序状态信息*/
                //CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "specstatusinfoid"), "specstatusinfo", "specstatusinfoid", toAppId, CommonMethods.OpType.新增);
                /*推送订单信息*/
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "mfgorderid"), "mfgorder", "mfgorderid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "ordertypeid"), "ordertype", "ordertypeid", toAppId, CommonMethods.OpType.新增);
                /*推送设备信息*/
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "resourceid"), "resourcedef", "resourceid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "resourcetypeid"), "resourcetype", "resourcetypeid", toAppId, CommonMethods.OpType.新增);
                /*推送设备人员信息*/
                var resourceEmp = @"SELECT e.employeeid FROM Resourcedef r
                    LEFT JOIN employeeresourcelist es ON es.resourcelistid = r.resourceid
                    LEFT JOIN employee e ON e.employeeid = es.employeeid
                    WHERE r.resourceid='{0}'";
                DataTable resourceEmpDT = OracleHelper.QueryTable(string.Format(resourceEmp, row["resourceid"]),null);
                CommonMethods.InsertTableChangeRecord(resourceEmpDT, "employee", "employeeid", toAppId, CommonMethods.OpType.新增);
                /*推送分厂信息*/
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "factoryid"), "factory", "factoryid", toAppId, CommonMethods.OpType.新增);
                /*推送排产明细信息*/
                CommonMethods.InsertTableChangeRecord(dispatchDV.ToTable(true, "apsplandetailid"), "apsplandetail", "apsplandetailid", toAppId, CommonMethods.OpType.新增);

                DataTable workflowDt = getWorkflowData(row["workflowstepid"].ToString());
                DataView workflowDV = workflowDt.AsDataView();
                /*推送工艺信息*/
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "workflowbaseid"), "workflowbase", "workflowbaseid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "workflowid"), "workflow", "workflowid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "workflowstepid"), "workflowstep", "workflowstepid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "specbaseid"), "specbase", "specbaseid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "specid"), "spec", "specid", toAppId, CommonMethods.OpType.新增);
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "spectypeid"), "spectype", "spectypeid", toAppId, CommonMethods.OpType.新增);//工序类型
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "stepcheckpointid"), "stepcheckpoint", "stepcheckpointid", toAppId, CommonMethods.OpType.新增);//工序检测项（含工步）
                CommonMethods.InsertTableChangeRecord(workflowDV.ToTable(true, "objectdocinfoid"), "objectdocinfo", "objectdocinfoid", toAppId, CommonMethods.OpType.新增);//工序工艺附件

                DataTable stepinfoDt = getStepInfoData(row["workflowstepid"].ToString());
                if (stepinfoDt.Rows.Count > 0)
                {
                    DataView stepinfoDV = stepinfoDt.AsDataView();
                    /*推送工步信息*/
                    CommonMethods.InsertTableChangeRecord(stepinfoDV.ToTable(true, "stepinfobaseid"), "stepinfobase", "stepinfobaseid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(stepinfoDV.ToTable(true, "stepinfoid"), "stepinfo", "stepinfoid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(stepinfoDV.ToTable(true, "spectypeid"), "spectype", "spectypeid", toAppId, CommonMethods.OpType.新增);//工步类型
                }
                /*-----------------------------------------------------
                 *-----------推送工步下的工装、辅材信息-------------------
                 *------------------------------------------------------*/
                string stepInfoIDs = string.Empty; //工步ID集
                foreach (DataRow item in stepinfoDt.AsDataView().ToTable(true, "stepinfoid").Rows)
                {
                    stepInfoIDs += item["stepinfoid"].ToString() + ",";
                }
                stepInfoIDs = stepInfoIDs.TrimEnd(',');
                DataTable dtTool = getWorkflowStepTools(stepInfoIDs);
                if (dtTool.Rows.Count > 0)
                {
                    DataView dvTool = dtTool.AsDataView();
                    /*推送工步的工具辅材关系表信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "stepinfotoolsid"), "stepinfotools", "stepinfotoolsid", toAppId, CommonMethods.OpType.新增);
                    /*推送工步的量具信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "measuretoolid"), "measuretool", "measuretoolid", toAppId, CommonMethods.OpType.新增);
                    /*推送工步的夹模具信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "specialtoolid"), "specialtool", "specialtoolid", toAppId, CommonMethods.OpType.新增);
                    /*推送工步的刀具信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "cuttoolid"), "cuttool", "cuttoolid", toAppId, CommonMethods.OpType.新增);
                    /*推送工步的辅材信息*/
                    CommonMethods.InsertTableChangeRecord(dvTool.ToTable(true, "auxmaterialid"), "auxmaterial", "auxmaterialid", toAppId, CommonMethods.OpType.新增);
                }

               /*-----------------------------------------------------
               *-----------推送bom信息-------------------
               *------------------------------------------------------*/
                string specids = string.Empty;
                foreach (DataRow item in workflowDV.ToTable(true, "specid").Rows) 
                {
                    specids += item["specid"].ToString() + ",";
                }
                DataTable bomDT = getBomData(row["bomid"].ToString(),specids,stepInfoIDs);
                if (bomDT.Rows.Count > 0)
                {
                    DataView bomDV = bomDT.AsDataView();
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "productmateriallistitemid"), "productmateriallistitem", "productmateriallistitemid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "productid"), "product", "productid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "productbaseid"), "productbase", "productbaseid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "bomid"), "bom", "bomid", toAppId, CommonMethods.OpType.新增);
                    CommonMethods.InsertTableChangeRecord(bomDV.ToTable(true, "bombaseid"), "bombase", "bombaseid", toAppId, CommonMethods.OpType.新增);
                }

            }
        }

        /// <summary>
        /// 获取需要下发到产线系统的工序级任务信息
        /// </summary>
        /// <returns></returns>
        private DataTable getDgWorkflowStepData()
        {
            string sql = @"select cdis.containerstepdispatchid ,cdis.containerid,r.resourceid,r.resourcetypeid,c4.customobjectitemsdesc toAppid,
                        c3.mfgorderid,m.ordertypeid,cdis.factoryid,apd.apsplandetailid,null specstatusinfoid,cdis.workflowstepid,c3.bomid
                        from containerstepdispatch cdis 
                        left join apsplandetail apd on apd.containerid =cdis.containerid and apd.workflowstepid =cdis.workflowstepid
                        left join resourcedef r on r.resourceid =apd.resourceid
                        left join customobject c2 ON c2.customobjectid=r.productline
                        left join customobjectitems c4 on c4.customobjectid =c2.customobjectid and c4.customobjectitemsname='toappid'--不为空
                        left join customobjectitems c5 on c5.customobjectid =c2.customobjectid and c5.customobjectitemsname='起草人'--不为空
                        left join customobjectitems c6 on c6.customobjectid =c2.customobjectid and c6.customobjectitemsname='审批人'--不为空
                        left join customobjectitems c7 on c7.customobjectid =c2.customobjectid and c7.customobjectitemsname='签发人'--不为空
                        left join containercurrentinfo c3 on c3.containerid =cdis.containerid
                        left join mfgorder m on m.mfgorderid =c3.mfgorderid
                        --left join specstatusinfo s on s.containerstepdispatchid =cdis.containerstepdispatchid 
                        where c2.customobjectname like 'CX-%' and cdis.isuse=0 --and c3.containerid='0004108000016122'
                        and c4.customobjectitemsdesc is not null 
                        and c5.customobjectitemsdesc is not null 
                        and c6.customobjectitemsdesc is not null 
                        and c7.customobjectitemsdesc is not null 
                        and cdis.createdate>to_date('2024-09-27','yyyy-MM-dd')
                        and c2.customobjectname <> 'CX-机匣柔性线'
                        and not exists (
                        	select 1 from sync_tablechangerecord st 
                        	left join sync_sendtoappid ss on ss.parentid=st.id 
                        	where ss.toappid=c4.customobjectitemsdesc and st.uniquecolvalue =cdis.containerstepdispatchid 
                            and st.tablename =upper('containerstepdispatch') and st.operatetype ='1'
                        	) order by cdis.createdate desc";
            return OracleHelper.QueryTable(sql, null);
        }
        /// <summary>
        /// 根据工序id,查询相关的工艺信息
        /// </summary>
        /// <param name="workflowstepid"></param>
        /// <returns></returns>
        private DataTable getWorkflowData(string workflowstepid)
        {
            string sql = @"select ws.workflowstepid,w.workflowid,w.workflowbaseid,sc.specbaseid,sc.specid,s.stepcheckpointid,sc.spectypeid,o.objectdocinfoid 
                        from workflowstep ws 
                        left join workflow w on w.workflowid =ws.workflowid 
                        left join specbase sb on sb.specbaseid = ws.specbaseid
                        left join spec sc on sc.specid = nvl(sb.revofrcdid,ws.specid)
                        left join stepcheckpoint s on s.workflowstepid =ws.workflowstepid
                        left join objectdocinfo o on (o.parentid =ws.workflowid or o.parentid=sc.specid) 
                        where ws.workflowstepid ='{0}'";
            return OracleHelper.QueryTable(string.Format(sql, workflowstepid), null);
        }
        /// <summary>
        /// 根据工序id,查询相关的工步信息
        /// </summary>
        /// <param name="workflowstepid"></param>
        /// <returns></returns>
        private DataTable getStepInfoData(string workflowstepid)
        {
            string sql = @"select s.stepinfoid,sib.stepinfobaseid,s.spectypeid 
                        from  stepinfo s 
                        inner join stepinfobase sib on sib.revofrcdid = s.stepinfoid --取最新版
                        where s.workflowstepid ='{0}'";
            return OracleHelper.QueryTable(string.Format(sql, workflowstepid), null);
        }
        /// <summary>
        /// 根据工步ids,查询相关的工装、辅材信息
        /// </summary>
        /// <param name="workflowstepid"></param>
        /// <returns></returns>
        private DataTable getWorkflowStepTools(string stepids)
        {
            string sql = string.Format(@"SELECT sit.stepinfotoolsid,mt.measuretoolid,st.specialtoolid,ct.cuttoolid,ax.auxmaterialid
                        FROM stepinfotools sit 
                        LEFT JOIN measuretool mt ON mt.measuretoolid = sit.tools
                        LEFT JOIN specialtool st ON st.specialtoolid = sit.tools
                        LEFT JOIN cuttool ct ON ct.cuttoolid = sit.tools
                        LEFT JOIN auxmaterial ax ON ax.auxmaterialid = sit.tools
                        WHERE sit.stepinfoid IN ('{0}')", stepids.Replace(",", "','"));
            return OracleHelper.QueryTable(sql, null);
        }

        /// <summary>
        /// 根据bomid/specids/stepids查询相关表数据
        /// </summary>
        /// <param name="bomid"></param>
        /// <returns></returns>
        private DataTable getBomData(string bomid,string specids,string stepids)
        {
            var sql = @"select pm.productmateriallistitemid,p.productid,pb.productbaseid,b.bomid,bb.bombaseid 
                    from productmateriallistitem pm
                    left join productbase pb1 on pb1.productbaseid=pm.productbaseid  
                    left join product p on p.productid=nvl(pb1.revofrcdid,pm.productid) 
                    left join productbase pb on pb.productbaseid=p.productbaseid 
                    left join bom b on b.bomid = pm.bomid
                    left join bombase bb on bb.bombaseid = b.bombaseid 
                    left join specbase sb1 on sb1.specbaseid=pm.specbaseid
                    left join spec s on s.specid=nvl(sb1.revofrcdid,pm.specid)
                    where pm.bomid='{0}' and (s.specid in ('{1}') or pm.stepinfoid in ('{2}'))";
            return OracleHelper.QueryTable(string.Format(sql, bomid,specids.Replace(",","','"),stepids.Replace(",","','")), null);
        }
    }
}
